USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[EkidenRankingTable]    スクリプト日付: 11/18/2024 13:12:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EkidenRankingTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[EkidenRankingTable]
GO

CREATE PROCEDURE [dbo].[EkidenRankingTable]
	@TAIKAINO INT,
	@KYOGINO INT,
	@SUBNO INT,
	@ROUNDNO INT,
	@KUMINO INT
AS
BEGIN

	-- ソートする区間を取得
	DECLARE @SQL nvarchar(max); 
	DECLARE @PARA nvarchar(max); 
	DECLARE @KUKAN int;

	SET @SQL = '
	BEGIN
	SELECT TOP 1 @KUKAN = LAP.ID
	FROM TBL_RESULT_LAP AS RLA

	LEFT JOIN TBL_KYOGI AS KYO
	ON RLA.TAIKAINO = KYO.TAIKAINO
	AND RLA.KYOGINO = KYO.KYOGINO

	LEFT JOIN TBL_LAP AS LAP
	ON KYO.SYUMOKUNO = LAP.SYUMOKUNO
	AND RLA.LAPNO =  LAP.LAPNO

	WHERE RLA.TAIKAINO = ' + STR(@TAIKAINO) + '
	AND RLA.KYOGINO = ' + STR(@KYOGINO) + '
	AND RLA.SUBNO = ' + STR(@SUBNO) + '
	AND RLA.ROUNDNO = ' + STR(@ROUNDNO) + '
	AND RLA.KUMINO = ' + STR(@KUMINO) + '
	AND RLA.KYOGIFLG2 = 1

	ORDER BY RLA.LAPNO DESC
	END
	'
	SET @PARA = '@KUKAN INT OUTPUT';

	EXECUTE sp_executesql @SQL, @PARA, @KUKAN OUTPUT;



	WITH EKIDEN_INFO AS (
		SELECT
			L.LANE,
			L.KYOGISYANO,
			KS.KYOGISYAMEI, 
			CASE SIGI.EXTEND4 WHEN '' THEN '9999' ELSE REPLACE(ISNULL(SIGI.EXTEND4,'9999'),'^0','9999') END AS EXTEND4
		FROM TBL_LANE L 
			INNER JOIN TBL_KYOGISYA KS  ON L.KYOGISYANO = KS.KYOGISYANO 
		INNER JOIN TBL_KYOGI K  ON L.TAIKAINO = K.TAIKAINO AND L.KYOGINO = K.KYOGINO 
		INNER JOIN TBL_LAP LP  ON K.SYUMOKUNO = LP.SYUMOKUNO
		LEFT JOIN TBL_RESULT_SIGI SIGI ON SIGI.LANE = L.LANE AND SIGI.SIGINO = LP.ID * 2
		WHERE L.TAIKAINO = @TAIKAINO
			AND L.KYOGINO = @KYOGINO
			AND L.SUBNO = @SUBNO
			AND L.ROUNDNO = @ROUNDNO
			AND L.KUMINO = @KUMINO
	)
	SELECT
		LANE,
		KYOGISYANO,
		KYOGISYAMEI,
		STUFF(
			(SELECT ',' + E2.EXTEND4
			 FROM EKIDEN_INFO E2
			 WHERE E2.KYOGISYANO = EKIDEN_INFO.KYOGISYANO
			 FOR XML PATH(''))
		,1,1,'') AS EXTEND4_LIST,
		CAST(@KUKAN AS VARCHAR) AS SORT
	FROM
		EKIDEN_INFO
	GROUP BY
		LANE,
		KYOGISYANO,
		KYOGISYAMEI
	ORDER BY
		LANE
END;